﻿using System;
using System.Collections.Generic;
using System.Data;
using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;


using LMD.Constant;
using LMD.Lib.DataClass;
using LMD.Lib.Oracle;

namespace Cached
{
    /// <summary>
    /// Summary description for Notification
    /// </summary>
    public class NotificationTable : CacheAbstraction
    {
        public NotificationData[] GetNotificationList(String sxUserId)
        {
            return GetNotificationList(sxUserId, 0, false);
        }
        public NotificationData[] GetNotificationList(String sxUserId, int iMaxCount, bool bForceRefresh)
        {
            DataTable dt = GetNotificationTable(sxUserId, bForceRefresh);
            List<NotificationData> lsNotif = new List<NotificationData>();

            int iLastNewsFeed = -1;
            NotificationData nfData = null;

            foreach (DataRow dr in dt.Rows)
            {
                int iCurrentNewsFeed = dr.ReadInt("T_NEWS_FEED_ID");

                if (iLastNewsFeed != iCurrentNewsFeed)
                {
                    if (nfData != null)
                    {
                        lsNotif.Add(nfData);

                        if (iMaxCount > 0)
                        {
                            if (lsNotif.Count >= iMaxCount)
                            {
                                nfData = null;
                                break;
                            }
                        }
                    }

                    nfData = new NotificationData();
                    nfData.NewsFeedID = iCurrentNewsFeed;
                    nfData.dtmDate = dr.ReadDateTime("TMP_DATE");
                    nfData.sxContent = String.Format(dr.ReadString("VALUE"), "<strong>" + dr.ReadString("OWNER_NAME") + "</strong>");
                    nfData.sxUserId = dr.ReadString("TRIGGERING_USER_ID");
                    nfData.sxUserName = dr.ReadString("TRIGGERING_USER_NAME");
                    nfData.IsRead = dr.ReadString("IS_READ") == KGlobal.YES;
                    nfData.AddUser(nfData.sxUserName);
                }
                else
                {
                    nfData.AddUser(dr.ReadString("TRIGGERING_USER_NAME"));
                }

                iLastNewsFeed = iCurrentNewsFeed;
            }

            if (nfData != null)
                lsNotif.Add(nfData);

            return lsNotif.ToArray();

        }

        public DataTable GetNotificationTable(String sxUserId)
        {
            return GetNotificationTable(sxUserId, false);
        }
        public DataTable GetNotificationTable(String sxUserId, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}_{1}", KCache.V_ICP_NOTIFICATION_MAPPING, sxUserId);

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                OracleParameter prm = new OracleParameter();
                prm.ParameterName = "prmID";
                prm.OracleDbType = OracleDbType.VarChar;
                prm.Value = sxUserId;

                String sxQuery =
                @"
                select
                    NOTIFIED_USER_ID,
                    IS_READ,
                    T_NEWS_FEED_ID,
                    TRIGGERING_USER_ID,
                    TRIGGERING_USER_NAME,
                    NOTIF_DATE,
                    POSTING_USER_ID,
                    POSTED_USER_ID,
                    CUSTOMER_ID,
                    OWNER_NAME,
                    VALUE,
                    MAX(NOTIF_DATE) over (partition by T_NEWS_FEED_ID) TMP_DATE
                from
                    V_ICP_NOTIFICATION_MAPPING
                where
                    NOTIFIED_USER_ID = :prmID
                order by
                    TMP_DATE desc,
                    T_NEWS_FEED_ID asc,
                    NOTIF_DATE desc";

                DataTable dt = dbl.GetDataTable(sxQuery, prm);

                AddToCache(sxCacheKey, dt, CacheType.FiveMinutes);

            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public int GetNotificationCount(String sxUserId)
        {
            DBLib dbl = new DBLib();

            OracleParameter prm = new OracleParameter();
            prm.ParameterName = "prmID";
            prm.OracleDbType = OracleDbType.VarChar;
            prm.Value = sxUserId;

            String sxQuery =
            @"
            select count(*) from
            (
                select distinct
                    notif.T_NEWS_FEED_ID
                from
                    MAP_NOTIFICATION_USER map
                    left join T_NOTIFICATION notif
                        on map.T_NOTIFICATION_ID = notif.T_NOTIFICATION_ID
                where
                    map.ICP_USER_ID = :prmID
                    and nvl(map.IS_READ, 'N') <> 'Y'
             )";
            int iReturn = Convert.ToInt32(dbl.GetSingleValue(sxQuery, prm));

            return iReturn;
        }


        public UserEventData[] GetEventList(String sxUserId)
        {
            return GetEventList(sxUserId, false);
        }
        public UserEventData[] GetEventList(String sxUserId, bool bForceRefresh)
        {
            DataTable dt = GetEventTable(sxUserId, bForceRefresh);
            List<UserEventData> lsNotif = new List<UserEventData>();

            UserEventData nfData = null;

            foreach (DataRow dr in dt.Rows)
            {
                nfData = new UserEventData();
                nfData.dtmDate = dr.ReadDateTime("EVENT_DATE");
                nfData.sxUserId = dr.ReadString("TRIGGERING_USER_ID");
                nfData.sxUserName = dr.ReadString("TRIGGERING_USER_NAME");
                nfData.iCompanyId = dr.ReadInt("TRIGGERING_CUSTOMER_ID");
                nfData.sxCompanyName = dr.ReadString("COMPANY_NAME");
                nfData.sxContent = dr.ReadString("EVENT_DESCRIPTION");
                nfData.sxURL = String.Format(dr.ReadString("URL"), dr.ReadString("EVENT_VALUE"));
                lsNotif.Add(nfData);
            }
            return lsNotif.ToArray();
        }

        public DataTable GetEventTable(String sxUserId)
        {
            return GetEventTable(sxUserId, false);
        }
        public DataTable GetEventTable(String sxUserId, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}_{1}", KCache.V_ICP_NOTIFICATION_MAPPING, sxUserId);

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                OracleParameter prm = new OracleParameter();
                prm.ParameterName = "prmID";
                prm.OracleDbType = OracleDbType.VarChar;
                prm.Value = sxUserId;

                String sxQuery =
                @"
                select
                    NOTIFIED_USER_ID,
                    T_USER_EVENT_ID,
                    TRIGGERING_USER_ID,
                    TRIGGERING_USER_NAME,
                    TRIGGERING_CUSTOMER_ID,
                    COMPANY_NAME,
                    EVENT_VALUE,
                    EVENT_DATE,
                    CODE,
                    EVENT_DESCRIPTION,
                    URL
                from
                    V_ICP_MAP_EVENT_USER
                where
                    NOTIFIED_USER_ID = :prmID
                    and to_char(EVENT_DATE, 'yyyy') = to_char(SysDate, 'yyyy')
                    and to_char(EVENT_DATE, 'MM') = to_char(SysDate, 'MM')
                    and to_char(EVENT_DATE, 'dd') = to_char(SysDate, 'dd')
                order by
                    T_USER_EVENT_ID desc";

                DataTable dt = dbl.GetDataTable(sxQuery, prm);

                AddToCache(sxCacheKey, dt, CacheType.FiveMinutes);

            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public int GetEventCount(String sxUserId)
        {
            DBLib dbl = new DBLib();

            OracleParameter prm = new OracleParameter();
            prm.ParameterName = "prmID";
            prm.OracleDbType = OracleDbType.VarChar;
            prm.Value = sxUserId;

            String sxQuery =
                @"
                select count(*) from
                (
                    select distinct
                        map.T_USER_EVENT_ID
                    from
                        MAP_EVENT_USER map
                        LEFT JOIN T_USER_EVENT t1
                            ON map.T_USER_EVENT_ID = t1.T_USER_EVENT_ID
                    where
                        ICP_USER_ID = :prmID
                        and to_char(t1.EVENT_DATE, 'yyyy') = to_char(SysDate, 'yyyy')
                        and to_char(t1.EVENT_DATE, 'MM') = to_char(SysDate, 'MM')
                        and to_char(t1.EVENT_DATE, 'dd') = to_char(SysDate, 'dd')
                 )";
            int i = Convert.ToInt32(dbl.GetSingleValue(sxQuery, prm));
            return i;
        }

        public override String GetCacheFilename()
        {
            return KCache.Filename.NOTIFICATION;
        }
    }
}